I didn’t write this post, a blogger named Keith did, but I am definitely going to link to his work on his blog.

Backing up PostgreSQL databases nightly on FreeBSD

He did a good job editing this script to backup postgresql on FreeBSD. While he does say he got some of it from another site he found on using Google…

…Using Google I found a generic script, after some custom tweaks for FreeBSD I had the script I wanted.

…so some of the work should be attributed elsewhere. I want to thank him for the work he did. This was something that was on my to do list and now it is pretty much done for me.

Here is the script he posted.

#!/bin/sh
# Location of binaries
bin="/usr/local/bin"
# Location of the backup logfile.
logfile="/data/backup/postgres/postgres.log"
# Location to place backups.
backup_dir="/data/backup/postgres"
username="pgsql"
database="template1"
touch $logfile
timeslot=`date +%H-%M`
databases=`$bin/psql -h localhost -d $database -U $username -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`

for i in $databases; do
        echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile
        $bin/vacuumdb -z -h localhost -U $username $i >/dev/null 2>&1
        j="60"
        while [ $j -ge 0 ]
        do
                temp0=`expr $j - 1`
                temp1=$j
                j=`expr $j - 1`
                if [ $temp0 -lt 0 ]
                then
                        suffix0=""
                        suffix1=".0"
                else
                        suffix0=".$temp0"
                        suffix1=".$temp1"
                fi
                if [ -f $backup_dir/postgresql-$i-database.gz$suffix0 ]
                then
                        echo "Renaming postgresql-$i-database.gz$suffix0 to postgresql-$i-database.gz$suffix1"
                        mv $backup_dir/postgresql-$i-database.gz$suffix0 $backup_dir/postgresql-$i-database.gz$suffix1
                fi
        done
        if [ $i != 'template0' ]
        then
                $bin/pg_dump -U $username $i -h 127.0.0.1 | gzip > "$backup_dir/postgresql-$i-database.gz"
        fi
done

Here is what I like about his script:

1. Variables are used at the top of the script that are easily modifiable.
2. It backs up all databases without having to list the databases, so you don’t have to update the list with every new database.
3. It does compress using gzip the databases, so uses as little space as possible.

Here are some ideas for future enhancements to the script

1. The gzip compression level as a variable up top. The default gzip compression level is 6 and 9 could make the file significantly smaller with large databases, while 1 would be faster for machines with weaker processors.
2. Database to exclude from the backup.  I like the idea to get them all by default, but sometimes you have a test database you just don’t care about and you don’t want a nightly backup of it.
3. I don’t see the password in the script, and I am not sure how it is authenticating without it, maybe a password feature needs to be added.